package edu.uoc.tdp.servidor.bbdd.informes;

import edu.uoc.tdp.bbdd.GestorDiscConnexio;
import edu.uoc.tdp.beans.informes.Universitat;
import edu.uoc.tdp.beans.proves.EstatProva;
import edu.uoc.tdp.beans.proves.Prova;
import edu.uoc.tdp.common.EOlympicsException;
import edu.uoc.tdp.common.EOlympicsExceptionProva;
import edu.uoc.tdp.common.EOlympicsUtils;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;

/**
 * Classe GestorInformes (Subsistema informes i estadistiques )
 * TDP Grup 1X4J
 */
public class GestorInformes {
    
    private GestorDiscConnexio gestor;
    private Connection connexio;

    /**
     * Constructor de la classe GestorInformes
     * Crea un nou gestor de disc.
     */
    public GestorInformes() throws EOlympicsException {
        this.gestor = new GestorDiscConnexio();
        this.gestor.obreConnexioBD();
        this.connexio = gestor.getConnexio();
    }

    /**
     * Constructor de la classe Gestora
     * Utilitza el gestor de disc passat com a paràmetre
     * @param gdc connexió a la BD de tipus GestorDiscConnexio
     */
    public GestorInformes(GestorDiscConnexio gdc) {
        this.gestor   = gdc;
        this.connexio = gdc.getConnexio();
    }
    
    
    /**
     * Accessor de lectura, recupera l'id i la descripció totes les proves en estat TANCADA
     * @return LLista de proves
     * @throws EOlympicsException 
     */
    public List<Prova> getProvesTancades() throws EOlympicsException{                
        String sql="SELECT P.idProva,P.descripcio "
                + "FROM PROVA P "                
                + "WHERE P.estat = '"  +EstatProva.CLASSIFICACIOFINAL.getEstat() +"'";
        List <Prova>proves= new ArrayList<Prova>();
        Statement st=gestor.creaSentencia();
        
        try{
            ResultSet rs=st.executeQuery(sql);
            while (rs.next()){                                
                proves.add(new Prova(rs.getInt(1),rs.getString(2)));
            }
            st.close();
            rs.close();
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
            
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }                        
        return proves;
    }
    
    
    
    /**
     * Accessor de lectura, recupera l'id i la descripció totes les proves inscrites de l'estudiant
     * @return LLista de proves
     * @throws EOlympicsException 
     */
    public List<Prova> getMevesProves(Integer idEstudiant) throws EOlympicsException{                
        String sql="SELECT P.idProva,P.descripcio "
                + "FROM PROVA P "                
                + "WHERE P.idProva in (select idprova from INSCRIPCIO "
                + " where idestudiant = "+idEstudiant+")"
                + " order by P.descripcio";
        List <Prova>proves= new ArrayList<Prova>();
        Statement st=gestor.creaSentencia();
        
        try{
            ResultSet rs=st.executeQuery(sql);
            while (rs.next()){                                
                proves.add(new Prova(rs.getInt(1),rs.getString(2)));
            }
            st.close();
            rs.close();
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
            
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }                        
        return proves;
    }
    
    /**
     * COnsulta de les proves amb estat tancat
     * @param idUniversitat
     * @param idProva
     * @param dinscripcio_ini
     * @param dinscripcio_fi
     * @param drealitacio_ini
     * @param drealitacio_fi
     * @return
     * @throws EOlympicsException 
     */
    public ArrayList<HashMap> consultarProves(String idUniversitat, Integer idProva, Date dinscripcio_ini,
            Date dinscripcio_fi, Date drealitacio_ini, Date drealitacio_fi) throws EOlympicsException{                
            String sql="SELECT P.descripcio, R.tipusRonda, R.nombrePreguntes, P.dataIniInscripcio, P.dataFiInscripcio, "+
                    " R.dataIniciRealitzacio, R.dataFiRealitzacio, P.nombrePlaces,R.idProva " +
                    " FROM RONDA R, PROVA P " +
                    " WHERE P.idProva = R.idProva "+
                    " AND P.estat = '"  +EstatProva.CLASSIFICACIOFINAL.getEstat() +"'";
            if (idProva!=null){
                sql+=" AND R.idProva = "  +idProva;
            }
            if (dinscripcio_ini!=null){
                sql+=" AND P.dataIniInscripcio >= '"  +EOlympicsUtils.getStringDateSimpleFormat(dinscripcio_ini) +"'";;
            }
            if (dinscripcio_fi!=null){
                sql+=" AND P.dataFiInscripcio <= '"  +EOlympicsUtils.getStringDateSimpleFormat(dinscripcio_fi) +"'";;
            }
            if (drealitacio_ini!=null){
                sql+=" AND R.dataIniciRealitzacio >= '"  +EOlympicsUtils.getStringDateSimpleFormat(drealitacio_ini) +"'";;
            }
            if (drealitacio_fi!=null){
                sql+=" AND R.dataFiRealitzacio <= '"  +EOlympicsUtils.getStringDateSimpleFormat(drealitacio_fi) +"'";;
            }
            
            
         
        ArrayList <HashMap>resultats= new ArrayList<HashMap>();
        Statement st=gestor.creaSentencia();
        
        try{
            ResultSet rs=st.executeQuery(sql);
            while (rs.next()){                                
                HashMap<String, Object> item = new HashMap<String, Object>();
                item.put("DESCRIPCIO", rs.getString(1));
                item.put("TIPUS", rs.getString(2));
                item.put("NUMPREGUNTES", rs.getInt(3));
                item.put("DINSCRIPCIO_INI", rs.getDate(4));
                item.put("DINSCRIPCIO_FI", rs.getDate(5));
                item.put("DREALITZACIO_INI", rs.getDate(6));
                item.put("DREALITZACIO_FI", rs.getDate(7));
                item.put("NUMPLACES", rs.getInt(8));
                item.put("IDPROVA", rs.getInt(9));
                
                item.put("INSCRITS", getInscritsByProva(idUniversitat,rs.getInt(9) ));
                item.put("PARTICIPANTS", getParticipantsByRonda(idUniversitat,rs.getInt(9), rs.getString(2) ));
                //Maxims
                String sqlMax ="select sum(respostesEncertades),sum(respostesFallades),sum(respostesBlanc), sum(nota) "+
                        "from PARTICIPACIO "+
                        "where idProva = "+rs.getInt(9)+
                        " and tipusRonda ='"+rs.getString(2) +"'";
                if (idUniversitat!=null){
                    sqlMax += " and idEstudiant in (select idEstudiant from ESTUDIANT where universitat = '"+idUniversitat+"')";
                }
                
                Statement stMaxims=gestor.creaSentencia();
                ResultSet rsMaxims=stMaxims.executeQuery(sqlMax);
                while (rsMaxims.next()){  
                    item.put("ENCERTS", rsMaxims.getInt(1));
                    item.put("ERRORS", rsMaxims.getInt(2));
                    item.put("BLANC", rsMaxims.getInt(3));
                    item.put("TOTAL", rsMaxims.getInt(4));
                }
                stMaxims.close();
                rsMaxims.close();
                
                //Notes
                
                String sqlNotes ="select  min(nota), max(nota) "+
                        "from PARTICIPACIO "+
                        "where idProva = "+rs.getInt(9)+
                        " and tipusRonda ='"+rs.getString(2) +"'";
                if (idUniversitat!=null){
                    sqlNotes += " and idEstudiant in (select idEstudiant from ESTUDIANT where universitat = '"+idUniversitat+"')";
                }
                Statement stNotes=gestor.creaSentencia();
                ResultSet rsNotes=stNotes.executeQuery(sqlNotes);
                while (rsNotes.next()){  
                    item.put("MIN", rsNotes.getInt(1));
                    item.put("MAX", rsNotes.getInt(2));
                }
                stNotes.close();
                rsNotes.close();
                resultats.add(item);
            }
            st.close();
            rs.close();
            
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
            
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }                        
        return resultats;
    }
    
    
    /**
     * Consulta les proves d'un estudiant amb estat tancat
     * @param tipus
     * @param idProva
     * @param dinscripcio_ini
     * @param dinscripcio_fi
     * @param drealitacio_ini
     * @param drealitacio_fi
     * @param idEstudiant
     * @return
     * @throws EOlympicsException 
     */
    public ArrayList<HashMap> consultarMevesProves(String tipus, Integer idProva, Date dinscripcio_ini,
            Date dinscripcio_fi, Date drealitacio_ini, Date drealitacio_fi, Integer idEstudiant) throws EOlympicsException{                
            String sql="SELECT P.descripcio, R.tipusRonda, R.nombrePreguntes, P.nombrePlaces,R.idProva " +
                    " FROM RONDA R, PROVA P " +
                    " WHERE P.idProva = R.idProva "+
                    " AND P.estat = '"  +EstatProva.CLASSIFICACIOFINAL.getEstat() +"'";
            if (idProva!=null){
                sql+=" AND R.idProva = "  +idProva;
            }
            if (dinscripcio_ini!=null){
                sql+=" AND P.dataIniInscripcio >= '"  +EOlympicsUtils.getStringDateSimpleFormat(dinscripcio_ini) +"'";;
            }
            if (dinscripcio_fi!=null){
                sql+=" AND P.dataFiInscripcio <= '"  +EOlympicsUtils.getStringDateSimpleFormat(dinscripcio_fi) +"'";;
            }
            if (drealitacio_ini!=null){
                sql+=" AND R.dataIniciRealitzacio >= '"  +EOlympicsUtils.getStringDateSimpleFormat(drealitacio_ini) +"'";;
            }
            if (drealitacio_fi!=null){
                sql+=" AND R.dataFiRealitzacio <= '"  +EOlympicsUtils.getStringDateSimpleFormat(drealitacio_fi) +"'";;
            }
            if (idEstudiant!=null){
                sql+=" AND P.idProva in (select idprova from INSCRIPCIO where idestudiant = "+idEstudiant+")";
            }
            if ((tipus!=null)&&(!tipus.equals(""))){
                sql+=" AND R.tipusRonda = '"+tipus+"'";
            }
                
        ArrayList <HashMap>resultats= new ArrayList<HashMap>();
        Statement st=gestor.creaSentencia();
        
        try{
            ResultSet rs=st.executeQuery(sql);
            while (rs.next()){   
                boolean trobat = false;
                HashMap<String, Object> item = new HashMap<String, Object>();
                item.put("DESCRIPCIO", rs.getString(1));
                item.put("TIPUS", rs.getString(2));
                item.put("NUMPREGUNTES", rs.getInt(3));
                item.put("NUMPLACES", rs.getInt(4));
                item.put("IDPROVA", rs.getInt(5));

                //Participació
                String sqlMax ="select respostesEncertades,respostesFallades,respostesBlanc, nota, dataparticipacio "+
                        "from PARTICIPACIO "+
                        "where idProva = "+rs.getInt(5)+
                        " and tipusRonda ='"+rs.getString(2) +"'"+
                        " and idestudiant ="+idEstudiant;
                Statement stMaxims=gestor.creaSentencia();
                ResultSet rsMaxims=stMaxims.executeQuery(sqlMax);
                while (rsMaxims.next()){  
                    trobat = true;
                    item.put("ENCERTS", rsMaxims.getInt(1));
                    item.put("ERRORS", rsMaxims.getInt(2));
                    item.put("BLANC", rsMaxims.getInt(3));
                    item.put("TOTAL", rsMaxims.getInt(4));
                    item.put("DATAPARTICIPACIO", rsMaxims.getDate(5));
                }
                stMaxims.close();
                rsMaxims.close();
                if (trobat){
                    resultats.add(item);
                }
            }
            st.close();
            rs.close();
            
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
            
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }                        
        return resultats;
    }
    
    /**
     * Retorna el número de incrists en una prova
     * @param idUniversitat
     * @param idProva
     * @return
     * @throws EOlympicsException 
     */
    public Integer getInscritsByProva(String idUniversitat, Integer idProva)throws EOlympicsException{
        Integer inscrits = null;
        try {
            String sql="select count(1) from INSCRIPCIO where idProva ="+idProva;
            if (idUniversitat!=null){
                sql += " and idEstudiant in (select idEstudiant from ESTUDIANT where universitat = '"+idUniversitat+"')";
            }

            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                inscrits = rsInscripcio.getInt(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            return inscrits;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    
    /**
     * Retorna el nombre de participants a una Ronda
     * @param idUniversitat
     * @param idProva
     * @param ronda
     * @return
     * @throws EOlympicsException 
     */
    public Integer getParticipantsByRonda(String idUniversitat, Integer idProva, String ronda)throws EOlympicsException{
        Integer inscrits = null;
        try {
            String sql="select count(1) from PARTICIPACIO where idProva ="+idProva + " and tipusronda='"+
                  ronda+"' "  ;
            if (idUniversitat!=null){
                sql += " and idEstudiant in (select idEstudiant from ESTUDIANT where universitat = '"+idUniversitat+"')";
            }
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                inscrits = rsInscripcio.getInt(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            return inscrits;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    /**
     * Retorna el medaller de EOlympics
     * @param uni
     * @return
     * @throws EOlympicsException 
     */
    public ArrayList<HashMap> consultarMedaller(Universitat uni) throws EOlympicsException{                
        ArrayList <Universitat> universitats  = new ArrayList<Universitat>();
        if (uni!=null){
            universitats.add(uni);
        }else {
            Universitat[] unis = Universitat.values();
            for (Universitat u : unis){
                universitats.add(u);
            }
        }
        ArrayList <HashMap>resultats= new ArrayList<HashMap>();
        
        for (Universitat universitat : universitats){
        
        try{
            String sql="SELECT posicioocupada, count(1) FROM GUANYADORRONDA WHERE tipusRonda = 'F' and posicioOcupada <=3";
            sql += " and idEstudiant in (select idEstudiant from ESTUDIANT where universitat = '"+universitat.getDescripcio()+"')";
            sql += " group by posicioocupada";
            Statement stMedaller=gestor.creaSentencia();
            ResultSet rsMedaller =stMedaller.executeQuery(sql);
            HashMap<String, Object> item = new HashMap<String, Object>();
                int or = 0;
                int plata = 0;
                int bronze = 0;
            while (rsMedaller.next()){
                
                
                Integer posicio = rsMedaller.getInt(1);
                if (posicio.equals(1)){
                    or = rsMedaller.getInt(2);
                }else if (posicio.equals(2)){
                    plata = rsMedaller.getInt(2);
                }else if (posicio.equals(3)){
                    bronze = rsMedaller.getInt(2);
                }
               
            }
            if (or+plata+bronze > 0 ){
                item.put("UNIVERSITAT", universitat.getDescripcio());
                item.put("OR", or);
                item.put("PLATA", plata);
                item.put("BRONZE", bronze);
                item.put("TOTAL", or+plata+bronze);
                resultats.add(item);
            }
            stMedaller.close();
            rsMedaller.close();
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }
        
        
        }
        return resultats;
    }
    /**
     * Tracta les excepcions SQL, diferenciant-les pel missatge de l'error
     * @param sqle                      ERROR.message()
     * @throws EOlympicsExceptionProva
     * @throws EOlympicsException 
     */
    private void treatExceptionSQL(String sqle) throws EOlympicsExceptionProva, EOlympicsException{
           if(sqle.contains("pk_prova")) {
               throw new EOlympicsExceptionProva(EOlympicsExceptionProva.ERR_SQL_PK_PROVA);
           }else if(sqle.contains("ck_datainsc")) {
               throw new EOlympicsExceptionProva(EOlympicsExceptionProva.ERR_SQL_CK_PROVA);           
           }
           else{
               throw new EOlympicsException(EOlympicsException.ERR_SQL);
           }
        
    }
    
    /**
     * Total de proves donades d’alta
     * @return
     * @throws EOlympicsException 
     */
    public Integer getProvasAlta()throws EOlympicsException{
        Integer inscrits = null;
        try {
            String sql="SELECT COUNT(1) FROM PROVA";
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                inscrits = rsInscripcio.getInt(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            return inscrits;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    /**
     * Total de proves en curs
     * @return
     * @throws EOlympicsException 
     */
    public Integer getProvaEnCurs()throws EOlympicsException{
        Integer inscrits = null;
        try {
            String sql="SELECT COUNT(1) FROM PROVA";
            sql += " WHERE ESTAT NOT IN ('"+EstatProva.CANCELADA.getEstat()+"','"+EstatProva.TANCADA.getEstat()+"','"+EstatProva.DESERTA.getEstat()+"','"+EstatProva.CLASSIFICACIOFINAL.getEstat()+"'"+")";
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                inscrits = rsInscripcio.getInt(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            return inscrits;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    
    
    /**
     * Total de proves finalitzades
     * @return
     * @throws EOlympicsException 
     */
    public Integer getProvaTancades()throws EOlympicsException{
        Integer inscrits = null;
        try {
            String sql="SELECT COUNT(1) FROM PROVA";
            sql += " WHERE ESTAT IN ('"+EstatProva.CLASSIFICACIOFINAL.getEstat()+"'"+")";
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                inscrits = rsInscripcio.getInt(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            return inscrits;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    
    
    /**
     * Nombre d’estudiants inscrits a les proves
     * @return
     * @throws EOlympicsException 
     */
    public Integer getNombreInscripcions()throws EOlympicsException{
        Integer inscrits = null;
        try {
            String sql="SELECT COUNT(1) FROM INSCRIPCIO";
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                inscrits = rsInscripcio.getInt(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            return inscrits;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
     /**
     * Mitjana d’estudiants inscrits per prova
     * @return
     * @throws EOlympicsException 
     */
    public BigDecimal getMitjanaInscrits()throws EOlympicsException{
        BigDecimal mitjana = null;
        Float inscrits = new Float (0);
        Float rondes = new Float (0);
        try {
            String sql="SELECT COUNT(1) FROM INSCRIPCIO";
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                inscrits = rsInscripcio.getFloat(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            
            String sqlRondes="SELECT COUNT(1) FROM RONDA";
            Statement stRonda=gestor.creaSentencia();
            ResultSet rsRondes =stRonda.executeQuery(sqlRondes);
            while (rsRondes.next()){
                rondes = rsRondes.getFloat(1);
            }
            stRonda.close();
            rsRondes.close();
            mitjana = new BigDecimal(inscrits/rondes);
            return mitjana;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    
    
    /**
     * Total de medalles a repartir
     * @return
     * @throws EOlympicsException 
     */
    public Integer getMedalles()throws EOlympicsException{
        Integer inscrits = null;
        try {
            String sql="SELECT count(1) FROM GUANYADORRONDA where posicioocupada <=3";
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                inscrits = rsInscripcio.getInt(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            return inscrits;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    
    /**
     * Puntuació mitjana en les proves
     * @return
     * @throws EOlympicsException 
     */
    public BigDecimal getMitjanaProves()throws EOlympicsException{
        Float suma = null;
        Float nombrepreguntes = null;
        try {
            String sql="select sum(nota),count(1) from PARTICIPACIO ";
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                suma = rsInscripcio.getFloat(1);
                nombrepreguntes = rsInscripcio.getFloat(2);
            }
            stInscripcio.close();
            rsInscripcio.close();
            suma = suma/nombrepreguntes;
            return new BigDecimal(suma);
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    /**
     * Percentatge de participació en proves
     * @return
     * @throws EOlympicsException 
     */
    public BigDecimal getPercentatgeParticipacio()throws EOlympicsException{
        
        BigDecimal mitjana = null;
        Float numplaces = null;
        Float inscrits = new Float(getNombreInscripcions());
        try {
            String sql="select sum(nombreplaces) from prova";
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                numplaces = rsInscripcio.getFloat(1);
            }
            stInscripcio.close();
            rsInscripcio.close();
            mitjana = new BigDecimal(inscrits/numplaces);
            return mitjana;
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;    
            
            
    }
    /**
     * Nombre de preguntes mitjà en les proves
     * @return
     * @throws EOlympicsException 
     */
    public BigDecimal getMitjanaPreguntesPerRonda()throws EOlympicsException{
        BigDecimal suma = null;
        BigDecimal nombrepreguntes = null;
        try {
            String sql="select sum(nombrepreguntes), count(1) from ronda ";
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                suma = rsInscripcio.getBigDecimal(1);
                nombrepreguntes = rsInscripcio.getBigDecimal(2);
            }
            stInscripcio.close();
            rsInscripcio.close();
            return suma.divide(nombrepreguntes);
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    
    /**
     * Percentatge de respostes encertades en proves
     * @return
     * @throws EOlympicsException 
     */
    public BigDecimal getPercentantgeRespostesEncertades()throws EOlympicsException{
        Float encertades = null;
        Float fallades = null;
        try {
            String sql="select sum(respostesEncertades), sum(respostesFallades), sum(respostesBlanc) from PARTICIPACIO ";
            Statement stInscripcio=gestor.creaSentencia();
            ResultSet rsInscripcio =stInscripcio.executeQuery(sql);
            while (rsInscripcio.next()){
                encertades = rsInscripcio.getFloat(1);
                fallades = rsInscripcio.getFloat(2);
                fallades += rsInscripcio.getFloat(3);
            }
            stInscripcio.close();
            rsInscripcio.close();
            if (encertades+fallades != 0){
                encertades = encertades/(encertades+fallades);
                return new BigDecimal(encertades);
            }else {
                return new BigDecimal(0);
            }
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }  
        return null;
    }
    
    
    /**
     * Retorna el detall d'una ronda
     * @param idProva
     * @param tipusRonda
     * @return
     * @throws EOlympicsException 
     */
    public HashMap consultarDetallProves(Integer idProva, String tipusRonda) throws EOlympicsException{                
            
        String sql="select p.descripcio, r.tipusronda, r.nombrepreguntes, r.datainicirealitzacio, r.datafirealitzacio, P.nombreplaces "+
                    " FROM RONDA R, PROVA P " +
                    " WHERE P.idProva = R.idProva "+
                    " and r.tipusRonda = '"+tipusRonda+"'"+
                    " and r.idprova = "+idProva;
         
        HashMap<String, Object> item = new HashMap<String, Object>();
        Statement st=gestor.creaSentencia();
        
        try{
            ResultSet rs=st.executeQuery(sql);
            while (rs.next()){                                
                
                item.put("DESCRIPCIO", rs.getString(1));
                item.put("TIPUS", rs.getString(2));
                item.put("NUMPREGUNTES", rs.getInt(3));
                item.put("DREALITZACIO_INI", rs.getDate(4));
                item.put("DREALITZACIO_FI", rs.getDate(5));
                item.put("NUMPLACES", rs.getInt(6));
                item.put("RESPOSTES", getRespostesByProva(idProva,tipusRonda ));
            }
            st.close();
            rs.close();
            
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
            
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }                        
        return item;
    }
    /**
     * Retorna les respostes d'una prova
     * @param idProva
     * @param tipusRonda
     * @return
     * @throws EOlympicsException 
     */
    public String getRespostesByProva(Integer idProva, String tipusRonda) throws EOlympicsException{                
        String retorn = "";
        String sql="select opciocorrecta from RESPOSTACORRECTA r "+
                    " where r.tipusRonda = '"+tipusRonda+"'"+
                    " and r.idprova = "+idProva;
        Statement st=gestor.creaSentencia();
        
        try{
            ResultSet rs=st.executeQuery(sql);
            while (rs.next()){                                
                if (retorn.equals("")){
                    retorn+=rs.getString(1);
                }else{
                    retorn+="-"+rs.getString(1);
                }
            }
            st.close();
            rs.close();
            
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
            
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }                        
        return retorn;
    }
    /**
     * Retorna la participació d'una prova
     * @param idProva
     * @param tipusProva
     * @param idUniversitat
     * @return
     * @throws EOlympicsException 
     */
    public ArrayList<HashMap> consultarParticipacio(Integer idProva, String tipusProva, String idUniversitat,Date drealitacio_ini, Date drealitacio_fi) throws EOlympicsException{                
            String sql="select p.dataparticipacio, respostesencertades, respostesfallades, respostesblanc, nota, e.universitat, u.nom||' '||u.cognom1||' '||u.cognom1 "+
                    " from participacio p, estudiant e, usuari u " +
                    " where p.idestudiant = e.idestudiant " +
                    " and e.idestudiant = u.idIntern  "+
                    " AND P.tipusronda = '"  +tipusProva +"'";
           if (idUniversitat!=null){
               sql += " and p.idEstudiant in (select idEstudiant from ESTUDIANT where universitat = '"+idUniversitat+"')";
           }
           if (drealitacio_ini!=null){
               sql+=" AND p.dataparticipacio >= '"  +EOlympicsUtils.getStringDateSimpleFormat(drealitacio_ini) +"'";;
           }
           if (drealitacio_fi!=null){
                sql+=" AND p.dataparticipacio <= '"  +EOlympicsUtils.getStringDateSimpleFormat(drealitacio_fi) +"'";;
           }
           sql += " order by p.nota desc";
        ArrayList <HashMap>resultats= new ArrayList<HashMap>();
        Statement st=gestor.creaSentencia();
        
        try{
            ResultSet rs=st.executeQuery(sql);
            while (rs.next()){                                
                HashMap<String, Object> item = new HashMap<String, Object>();
                item.put("DATA", rs.getDate(1));
                item.put("ENCERTS", rs.getInt(2));
                item.put("ERRORS", rs.getInt(3));
                item.put("BLANCS", rs.getInt(4));
                item.put("TOTAL", rs.getInt(5));
                item.put("UNIVERSITAT", rs.getString(6));
                item.put("ESTUDIANT", rs.getString(7));
                resultats.add(item);
            }
            st.close();
            rs.close();
            
        }catch (SQLException sqle){
            treatExceptionSQL(sqle.getMessage());
            
        }catch (Exception e){
            throw new EOlympicsException(EOlympicsException.ERR_DESC_EXEC);
        }                        
        return resultats;
    }
}
